import pymysql
from collections import defaultdict
from export.db_config import db_config
import pandas as pd

connection = pymysql.connect(**db_config)
cursor = connection.cursor()

def search():
    # 模拟从数据库获取数据
    query = "SELECT * FROM statistics_clct_subject"
    cursor.execute(query)
    results = cursor.fetchall()

    for desc in cursor.description:
        print(desc)

    # 获取列名
    columns = [desc[0] for desc in cursor.description]

    # 转换为 DataFrame
    df = pd.DataFrame(results, columns=columns)

    # 显示 DataFrame 中数值列的描述性统计信息，如计数、平均值、标准差、最小值、最大值等。
    print(df.info())

    # # 通过列名访问字段值
    # first_name = df.loc[0, 'site_id']
    # print(first_name)

    # for row in results:
    #     print(row)

    # print(len(results))
    return results

search()

# cursor.close()
# connection.close()
#
# class StatisticsClctSubjectRepository:
#     def search(self):
#         # 模拟从数据库获取数据
#         query = "SELECT * FROM statistics_clct_subject"
#         cursor.execute(query)
#         results = cursor.fetchall()
#         # for row in results:
#         #     print(row)
#
#         # print(len(results))
#         return results

    # def query_modality_first_clct(self, project_id, modality_id, subject_id):
    #     # 模拟从数据库获取首次采集日期
    #     return [
    #         {'site_id': '1', 'subject_id': 'subject1', 'firstClct': datetime(2023, 1, 1)},
    #         {'site_id': '1', 'subject_id': 'subject2', 'firstClct': datetime(2023, 2, 1)}
    #     ]
#
# def get_site_id_to_stat_map():
#     results = StatisticsClctSubjectRepository().search()
#     print(len(results))
#     stat_list = results
#     site_id_to_stat_map = defaultdict()
#     for stat in stat_list:
#         print(stat)
#         print(stat[0])
#         print(stat[1])
#         site_id_to_stat_map[stat.site_id].append(stat)
#
#     list = site_id_to_stat_map.get('BNU', [])
#
#     for stat in list:
#         print(stat)
#
#     return site_id_to_stat_map
#
# get_site_id_to_stat_map()